Imports Microsoft.VisualBasic

Public Class Executive

    Private ExecutiveID As Integer
    Private ExecutiveName As String
    Private ExecutiveEmail As String
    Private Designation As String
    Private Telephone As String
    Private Location As String
    Private Manager As Integer
    Private DepartmentID As Integer
    Private SuperiorID As Integer



    ''' <summary>
    ''' Creates an Executive with an already existing ID
    ''' </summary>
    Public Sub New(ByVal ID As Integer)
        ExecutiveID = ID
        ExecutiveName = Nothing
        ExecutiveEmail = Nothing
        Designation = Nothing
        Telephone = Nothing
        Location = Nothing
        Manager = -1
        DepartmentID = -1
        SuperiorID = Nothing

    End Sub

    ''' <summary>
    ''' Creates a blank executive who can do nothing but be created with the supplied parameters
    ''' </summary>
    Public Sub New(ByVal Name As String, ByVal email As String, ByVal Desg As String, ByVal Tel As String, ByVal Loc As String, ByVal Man As Integer, ByVal DepID As Integer, ByVal Superior As Integer)

        'ExecutiveID as -1 means that the Executive has not been created yet
        ExecutiveID = -1
        ExecutiveName = Name
        ExecutiveEmail = email
        Designation = Desg
        Telephone = Tel
        Location = Loc
        Manager = Man
        DepartmentID = DepID
        SuperiorID = Superior

    End Sub

    ''' <summary>
    ''' Creates an Executive with the supplied Parameters. Returns false if the executive has already been created
    ''' </summary>
    Public Function Create() As Boolean


        If CheckValidExecutive() = True Then

            'The ExecutiveID has already been assigned and cannot be recreated
            Return False
        End If

        Dim Params(7) As GenParameter
        Dim Sql As String
        Dim LastID As Integer

        Sql = "INSERT INTO ExecutiveMaster(ExecutiveName, email, Designation, Location, ExecutiveUserName, UserPassword, Telephone, AccessID, Active, DepartmentID, Locked, SupervisorID)" & _
            " VALUES (?ExecutiveName, ?email, ?Designation, ?Location, ?email, sha1('test123'), ?Telephone, ?Manager, 1, ?DepartmentID, 0, ?SupervisorID)" & _
            " ;Select LAST_INSERT_ID() AS Id"

        Params(0) = New GenParameter("?ExecutiveName", ExecutiveName, Data.DbType.String)
        Params(1) = New GenParameter("?email", ExecutiveEmail, Data.DbType.String)
        Params(2) = New GenParameter("?Designation", Designation, Data.DbType.String)
        Params(3) = New GenParameter("?Location", Location, Data.DbType.String)
        Params(4) = New GenParameter("?Telephone", Telephone, Data.DbType.String)
        Params(5) = New GenParameter("?Manager", Manager, Data.DbType.Boolean)
        Params(6) = New GenParameter("?DepartmentID", DepartmentID, Data.DbType.Int16)
        Params(7) = New GenParameter("?SupervisorID", SuperiorID, Data.DbType.Int16)

        Try

            LastID = GenHelper.ExecuteScalar(Sql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

        Catch ex As Exception

            Return False

        End Try

        'Assign the ExecutiveID to the newly created ID

        ExecutiveID = LastID

        'Insert the department into the Roles Table

        ReDim Params(1)

        Sql = "INSERT INTO RoleMaster (ExecutiveID, DepartmentID)" & _
            " VALUES (?ExecutiveID, ?DepartmentID)"

        Params(0) = New GenParameter("?ExecutiveID", LastID, Data.DbType.Int16)
        Params(1) = New GenParameter("?DepartmentID", DepartmentID, Data.DbType.Int16)

        GenHelper.ExecuteNonQuery(Sql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

        Return True

    End Function

    ''' <summary>
    ''' Changes the details of the Executives with the specified Parameters. Returns false if the ExecutiveID is -1 for the object
    ''' </summary>
    Public Function ChangeDetails(ByVal Name As String, ByVal email As String, ByVal Desg As String, ByVal Tel As String, ByVal Loc As String, ByVal Man As Integer, ByVal DepID As Integer, ByVal Superior As Integer) As Boolean

        If CheckValidExecutive() = False Then
            Return False
        End If

        Dim Params(8) As GenParameter
        Dim Sql As String

        Sql = "UPDATE ExecutiveMaster" & _
            " SET ExecutiveName = ?ExecutiveName, ExecutiveUserName = ?email, email = ?email, Designation = ?Designation, Location = ?Location, Telephone = ?Telephone, AccessID = ?Manager, DepartmentID = ?DepartmentID, SupervisorID = ?SupervisorID" & _
            " WHERE ExecutiveID = ?ExecutiveID"

        Params(0) = New GenParameter("?ExecutiveName", Name, Data.DbType.String)
        Params(1) = New GenParameter("?email", email, Data.DbType.String)
        Params(2) = New GenParameter("?Designation", Desg, Data.DbType.String)
        Params(3) = New GenParameter("?Location", Loc, Data.DbType.String)
        Params(4) = New GenParameter("?Telephone", Tel, Data.DbType.String)
        Params(5) = New GenParameter("?Manager", Man, Data.DbType.Boolean)
        Params(6) = New GenParameter("?DepartmentID", DepID, Data.DbType.Int16)
        Params(7) = New GenParameter("?ExecutiveID", ExecutiveID, Data.DbType.Int32)
        Params(8) = New GenParameter("?SupervisorID", Superior, Data.DbType.Int16)

        Try
            GenHelper.ExecuteNonQuery(Sql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

        Catch ex As Exception

            Return False

        End Try

        Return True

    End Function

    ''' <summary>
    ''' Resets the password of the initialized Executive
    ''' </summary>
    Public Function ResetPassword() As Boolean
        Dim Sql As String
        Dim Params(0) As GenParameter

        If CheckValidExecutive() = False Then
            Return False
        End If

        Sql = "UPDATE ExecutiveMaster" & _
            " SET UserPassword = sha1('test123')" & _
            " WHERE ExecutiveID = ?ExecutiveID"

        Params(0) = New GenParameter("?ExecutiveID", ExecutiveID, Data.DbType.Int32)

        Try

            GenHelper.ExecuteNonQuery(Sql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

        Catch ex As Exception

            Return False

        End Try

        Return True
    End Function

    ''' <summary>
    ''' Deactivates the initialized executive
    ''' </summary>
    Public Function Deactivate() As Boolean

        Dim Sql As String
        Dim Params(0) As GenParameter

        If CheckValidExecutive() = False Then
            Return False
        End If

        Sql = "UPDATE ExecutiveMaster" & _
            " SET Active = 0" & _
            " WHERE ExecutiveID = ?ExecutiveID"

        Params(0) = New GenParameter("?ExecutiveID", ExecutiveID, Data.DbType.Int32)

        Try

            GenHelper.ExecuteNonQuery(Sql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

        Catch ex As Exception

            Return False

        End Try

        Return True

    End Function

    ''' <summary>
    ''' Assigns or Removes Roles to Executives. Throws several exceptions for different error conditions
    ''' </summary>
    ''' <param name="RoleName">Name of the Role as String</param>
    ''' <param name="Remove">Property to determine if the role is to be assigned or to be removed. True for Removal, False for Assignment</param>
    Public Function AssignRole(ByVal RoleName As String, ByVal Remove As Boolean) As Boolean

        Dim Sql As String
        Dim Params(1) As GenParameter
        Dim RoleID As Integer
        Dim Value As Integer
        Dim Success As Boolean

        'Check to see if all the parameters are met

        If CheckValidExecutive() = False Then
            Throw New System.InvalidOperationException("Invalid Executive")
            Return False

        End If

        Sql = "Select DepartmentID FROM DepartmentMaster WHERE DepartmentName = '" & RoleName & "'"
        RoleID = GenHelper.ExecuteScalar(Sql, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

        If RoleID = 0 Then
            Throw New System.InvalidOperationException("Invalid Role")
            Return False
        End If

        Sql = "Select Count(*) FROM RoleMaster WHERE ExecutiveID = " & ExecutiveID & _
            " AND DepartmentID = " & RoleID

        Value = GenHelper.ExecuteScalar(Sql, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

        If Value <> 0 And Remove = False Then
            Throw New System.InvalidOperationException("Already Assigned")
            Return False
        End If

        If Value = 0 And Remove = True Then
            Throw New System.InvalidOperationException("Not Yet Assigned")
            Return False
        End If

        If Remove = False Then

            Sql = "INSERT INTO RoleMaster (ExecutiveID, DepartmentID)" & _
            " VALUES (?ExecutiveID, ?DepartmentID)"

            Params(0) = New GenParameter("?ExecutiveID", ExecutiveID, Data.DbType.Int32)
            Params(1) = New GenParameter("?DepartmentID", RoleID, Data.DbType.Int32)

            Success = GenHelper.ExecuteNonQuery(Sql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

            Return Success

        Else

            Sql = "DELETE FROM RoleMaster" & _
            " WHERE ExecutiveID = ?ExecutiveID AND DepartmentID = ?DepartmentID"

            Params(0) = New GenParameter("?ExecutiveID", ExecutiveID, Data.DbType.Int32)
            Params(1) = New GenParameter("?DepartmentID", RoleID, Data.DbType.Int32)

            Success = GenHelper.ExecuteNonQuery(Sql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

            Return Success


        End If

    End Function

    ''' <summary>
    ''' Returns True if the ExecutiveID is not -1
    ''' </summary>
    Protected Function CheckValidExecutive() As Boolean

        If ExecutiveID = -1 Then
            Return False
        Else
            Return True
        End If

    End Function

    ''' <summary>
    ''' Assigns or Removes a Project from an employee
    ''' </summary>
    ''' <param name="PID">ID of the Project</param>
    ''' <param name="Remove">True to Remove Project. False for Adding Project</param>
    Public Function AssignProject(ByVal PID As Integer, ByVal Remove As Boolean) As Boolean

        Dim Sql As String
        Dim Params(1) As GenParameter
        Dim Value As Integer
        Dim Success As Boolean

        'Check to see if all the parameters are met

        If CheckValidExecutive() = False Then
            Throw New System.InvalidOperationException("Invalid Executive")
            Return False

        End If

        Sql = "Select Count(*) FROM ProjectMaster WHERE ProjectID = '" & PID & "'"
        Value = GenHelper.ExecuteScalar(Sql, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

        If Value = 0 Then
            Throw New System.InvalidOperationException("Invalid Project ID")
            Return False
        End If

        Sql = "Select Count(*) FROM ProjectAssignments WHERE ExecutiveID = " & ExecutiveID & _
            " AND ProjectID = " & PID

        Value = GenHelper.ExecuteScalar(Sql, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

        If Value <> 0 And Remove = False Then
            Throw New System.InvalidOperationException("Already Assigned")
            Return False
        End If

        If Value = 0 And Remove = True Then
            Throw New System.InvalidOperationException("Not Yet Assigned")
            Return False
        End If

        If Remove = False Then

            Sql = "INSERT INTO ProjectAssignments (ExecutiveID, ProjectID)" & _
            " VALUES (?ExecutiveID, ?ProjectID)"

            Params(0) = New GenParameter("?ExecutiveID", ExecutiveID, Data.DbType.Int32)
            Params(1) = New GenParameter("?ProjectID", PID, Data.DbType.Int32)

            Success = GenHelper.ExecuteNonQuery(Sql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

            Return Success

        Else

            Sql = "DELETE FROM ProjectAssignments" & _
            " WHERE ExecutiveID = ?ExecutiveID AND ProjectID = ?ProjectID"

            Params(0) = New GenParameter("?ExecutiveID", ExecutiveID, Data.DbType.Int32)
            Params(1) = New GenParameter("?ProjectID", PID, Data.DbType.Int32)

            Success = GenHelper.ExecuteNonQuery(Sql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

            Return Success


        End If

    End Function

    ''' <summary>
    ''' Assigns a specific Client to the Executive
    ''' </summary>
    Public Function AssignClient(ByVal CID As Int64) As Boolean

        Dim Sql As String
        Dim Params(0) As GenParameter
        Dim Value As Integer
        Dim TDate As Date
        Dim RightNow As Date
        Dim Success As Boolean

        RightNow = Now.AddHours(10)
        RightNow = RightNow.AddMinutes(30)
        TDate = RightNow.Year & "/" & RightNow.Month & "/" & RightNow.Day

        'Check if the current executive is valid

        If CheckValidExecutive() = False Then
            Throw New System.InvalidOperationException("Invalid Executive")
            Return False

        End If

        'Check if the ID supplied is valid

        Sql = "Select COUNT(*) FROM ClientMaster WHERE ClientID = ?CID"
        Params(0) = New GenParameter("?CID", CID, Data.DbType.Int64)
        Value = GenHelper.ExecuteScalar(Sql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

        If Value <> 1 Then
            Throw New System.InvalidOperationException("Invalid Client")
            Return False
        End If

        'Check if the Executive already has this Client Assigned to them

        ReDim Params(1)

        Sql = "Select COUNT(*) FROM CompanyAssignments WHERE ExecutiveID = ?EID AND ClientID = ?CID"
        Params(0) = New GenParameter("?EID", ExecutiveID, Data.DbType.Int32)
        Params(1) = New GenParameter("?CID", CID, Data.DbType.Int64)

        Value = GenHelper.ExecuteScalar(Sql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

        If Value > 0 Then
            Throw New System.InvalidOperationException("Already assigned to Executive")
            Return False
        End If

        'Proceed with Assignment

        ReDim Params(2)

        Sql = "INSERT INTO CompanyAssignments (ExecutiveID, ClientID, CADate)" & _
            " VALUES (?EID, ?CID, ?CADate)"

        Params(0) = New GenParameter("?EID", ExecutiveID, Data.DbType.Int32)
        Params(1) = New GenParameter("?CID", CID, Data.DbType.Int64)
        Params(2) = New GenParameter("?CADate", TDate, Data.DbType.Date)

        Success = GenHelper.ExecuteNonQuery(Sql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

        Return Success

    End Function

    ''' <summary>
    ''' Assign all the clients of one executive to another
    ''' </summary>
    ''' <param name="PrimaryExecID">The ID of the Executive to whom the client originally belongs</param>
    Public Function AssignExecutiveClients(ByVal PrimaryExecID As Int32) As Boolean

        Dim Sql As String
        Dim Params(0) As GenParameter
        Dim Value As Integer
        Dim TDate As Date
        Dim RightNow As Date
        Dim Success As Boolean

        RightNow = Now.AddHours(10)
        RightNow = RightNow.AddMinutes(30)
        TDate = RightNow.Year & "/" & RightNow.Month & "/" & RightNow.Day

        'Check if the current executive is valid

        If CheckValidExecutive() = False Then
            Throw New System.InvalidOperationException("Invalid Executive")
            Return False

        End If

        'Check if the Primary Executive exists

        Sql = "Select COUNT(*) FROM ExecutiveMaster WHERE ExecutiveID = ?PrimaryExecID AND Active = 1"
        Params(0) = New GenParameter("?PrimaryExecID", PrimaryExecID, Data.DbType.Int32)
        Value = GenHelper.ExecuteScalar(Sql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

        If Value <> 1 Then
            Throw New System.InvalidOperationException("Primary Executive does not exist")
            Return False
        End If

        ' Check if the two ID's are the same

        If ExecutiveID = PrimaryExecID Then
            Throw New System.InvalidOperationException("You cannot assign Executives to themselves")
            Return False
        End If

        ' Perform the Insertion

        ReDim Params(2)

        Sql = "INSERT IGNORE INTO CompanyAssignments (ExecutiveID, ClientID, CADate)" & _
            " SELECT ?SecondaryExecID, ClientID, ?CADate FROM CompanyAssignments" & _
            " WHERE ExecutiveID = ?PrimaryExecID"
        Params(0) = New GenParameter("?PrimaryExecID", PrimaryExecID, Data.DbType.Int32)
        Params(1) = New GenParameter("?SecondaryExecID", ExecutiveID, Data.DbType.Int32)
        Params(2) = New GenParameter("?CADate", TDate, Data.DbType.Date)

        Success = GenHelper.ExecuteNonQuery(Sql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())

        Return Success

    End Function

End Class
